<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;
class Administration extends SessionObject {
    /**
     * lookupAndUpdateDatabaseConf($name, &$conf)
     * This routine is called when a database $conf has been determined from code or config.php.
     * A trip to the database is required to ensure that there is a db_id allocated for this database.
     * While we are at it, we validate that the values in the database are accurate.
     * If necessary, we update values in 
     *    * auth.db
     *    * auth.db_instance
     *    * auth.db_connection
     *    * meta.db
     *    * meta.db_instance
     *    * meta.db_connection
     * It is possible that the $conf is completely empty. The only thing absolutely required is the $name.
     * @param string $name
     * @param array  &$conf
     */
    public function lookupAndUpdateDatabaseConf($name, &$conf) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($conf)) $conf = array();
        $authdb = $this->context->authdb();

        #if (isset($conf['db_name']) && $name !== $conf['db_name']) throw new \Exception("You requested a database named $name but its name in the configuration appears to be ".$conf['db_name']);

        $db_select = 'select db.db_id, db.owner_group_id, db.db_env, db.db_name, db.db_label, db.db_desc, db.db_connection_id, ' .
                     'dbc.db_instance_id, dbc.db_connection_name, db.db_owner_group_id, dbc.default_schema, ' .
                     'db.dbtype, db.schema_name, db.status, ' .
                     'dbc.dbhost, dbc.db_class, dbc.db_class as class, dbc.dsn, dbc.dbuser, dbc.dbpass, dbc.dboptions, ' .
                     'db.reserved_size, db.est_current_size, db.last_refresh_dttm, db.user_mode, db.group_mode, db.other_mode ' .
                     'from {auth_schema_}db inner join {auth_schema_}db_connection dbc on dbc.db_connection_id = db.db_connection_id';

        if (isset($conf['db_id'])) {
            $db_id = $conf['db_id'];
            $db_conf = $authdb->get_hash("$db_select where db.db_id = ?", $db_id);
            if (isset($db_conf)) {
                $owner_group_id = $db_conf['owner_group_id'];
                $db_env         = $db_conf['db_env'];
                $db_name        = $db_conf['db_id'];
                if ($name !== $db_name) throw new \Exception("You requested a database identified as $db_id and named $name but its name appears to be $db_name");
                if (isset($conf['db_env']) && $db_env !== $conf['db_env']) throw new \Exception("You requested a database with db_env=$db_env but its db_env in the configuration appears to be ".$conf['db_env']);
                if (isset($conf['owner_group_id']) && $owner_group_id != $conf['owner_group_id']) throw new \Exception("You requested a database with owner_group_id=$owner_group_id but its owner_group_id in the configuration appears to be ".$conf['owner_group_id']);
            }
        }
        else {
            if (isset($conf['owner_group_id']) && $conf['owner_group_id']) { $owner_group_id = $conf['owner_group_id']; }
            else                                                           { $owner_group_id = APP_ALLUSERS_GROUP_ID; $conf['owner_group_id'] = $owner_group_id; }
            if (isset($conf['db_env'])         && $conf['db_env'])         { $db_env = $conf['db_env']; }
            else                                                           { $db_env = $this->context->db_env(); $conf['db_env'] = $db_env; }
            if (isset($conf['db_name'])        && $conf['db_name'])        { $db_name = $conf['db_name']; }
            else                                                           { $db_name = $name; $conf['db_name'] = $db_name; }

            $db_conf = $authdb->get_hash("$db_select where db.owner_group_id = ? and db.db_env = ? and db.db_name = ?", array($owner_group_id, $db_env, $db_name));
            if (isset($db_conf)) {
                $db_id = $db_conf['db_id'];
            }
        }

        # if we did find it in the database, check to see if we need to update any values
        if (isset($db_conf)) {
            $dbi_updatable_columns = array('db_instance_id', 'db_instance_name', 'db_owner_group_id', 'dbtype', 'dbhost');
            $dbc_updatable_columns = array('db_connection_id', 'db_connection_name', 'db_instance_id', 'db_owner_group_id',
                                           'dbtype', 'dbhost', 'default_schema', 'db_class', 'dsn', 'dbuser', 'dbpass', 'dboptions');
            $db_updatable_columns  = array('db_label', 'db_desc', 'dbtype', 'db_connection_id', 'schema_name', 'status');

            $dbi_updated_columns    = array();
            $dbi_updated_values     = array();
            $dbc_updated_columns    = array();
            $dbc_updated_values     = array();
            $db_updated_columns     = array();
            $db_updated_values      = array();

            foreach ($conf as $var => $value) {
                if (isset($value) && $value !== '' && array_key_exists($var, $db_conf) && $value != $db_conf[$var]) {
                    if (in_array($var, $dbi_updatable_columns)) {
                        $dbi_updated_columns[] = $var;
                        $dbi_updated_values[]  = $value;
                    }
                    if (in_array($var, $dbc_updatable_columns)) {
                        $dbc_updated_columns[] = $var;
                        $dbc_updated_values[]  = $value;
                    }
                    if (in_array($var, $db_updatable_columns)) {
                        $db_updated_columns[]  = $var;
                        $db_updated_values[]   = $value;
                    }
                    $db_conf[$var] = $value;
                }
            }
            # TODO - finish the update step
            foreach ($db_conf as $var => $value) {
                $conf[$var] = $value;
            }
        }
        # if we did NOT find it in the database, create it
        else {
            unset($conf['db_id']);
            unset($conf['db_connection_id']);
            unset($conf['db_instance_id']);
            if (isset($conf['db_connection_name']) && $conf['db_connection_name']) {
                $db_connection = $authdb->get_hash('select db_instance_id, db_owner_group_id, dbtype, default_schema, dbhost, db_class, dsn, dbuser, dbpass, dboptions from {auth_schema_}db_connection where db_connection_name = ?', $conf['db_connection_name']);
                if (isset($db_connection)) {
                    foreach ($db_connection as $var => $value) {
                        $conf[$var] = $db_connection[$var];
                    }
                }
                else {
                    throw new \Exception("No db_connection found for '$db_connection_name' in the '$name' database configuration");
                }
                if (!isset($conf['db_owner_group_id'])) $conf['db_owner_group_id'] = APP_SYSTEM_GROUP_ID;
                if (!isset($conf['user_mode']))  $conf['user_mode']  = 7;
                if (!isset($conf['group_mode'])) $conf['group_mode'] = 7;
                if (!isset($conf['other_mode'])) $conf['other_mode'] = 6;
                #$authdb->insert('db_instance', $dbi_insert_columns, $conf);
                #  db_instance_id           INTEGER          NOT NULL constraint db_instance_pk primary key autoincrement,
                #insert into {auth_schema_}db_instance   (db_instance_name, owner_group_id, db_owner_group_id, dbtype, dbhost,
                #                           user_mode, group_mode, other_mode, modify_user_id, create_user_id)
                #  db_connection_id         INTEGER          NOT NULL constraint db_connection_pk primary key autoincrement,
                #insert into {auth_schema_}db_connection (db_connection_name, owner_group_id, db_instance_id, db_owner_group_id, dbtype, dbhost, default_schema, db_class, dsn, dbuser, dbpass, dboptions,
                #                           user_mode, group_mode, other_mode, modify_user_id, create_user_id)
                #  db_id                    INTEGER          NOT NULL constraint db_pk primary key autoincrement,
                #insert into {auth_schema_}db (owner_group_id, db_env, db_name, db_label, db_desc, dbtype, db_connection_id, schema_name, status, reserved_size, est_current_size, last_refresh_dttm,
                #                           user_mode, group_mode, other_mode, modify_user_id, create_user_id)
            }
            else {
                throw new \Exception("No db_connection_name provided in the '$name' database configuration");
            }
        }

        if (!isset($conf['db_env'])         || !$conf['db_env'])         $conf['db_env']         = $this->context->db_env();
        if (!isset($conf['owner_group_id']) || !$conf['owner_group_id']) $conf['owner_group_id'] = APP_ALLUSERS_GROUP_ID;
        $db_env         = $conf['db_env'];
        $owner_group_id = $conf['owner_group_id'];

        if (isset($db_conf)) {
            #$db->db_id            = $db_conf['db_id'];
            #$db->db_connection_id = $db_conf['db_connection_id'];
            #$db->owner_group_id   = $db_conf['owner_group_id'];
            #$fix_columns = array();
            #$fix_values  = array();
            #foreach ($conf as $key => $value) {
            #    if (array_key_exists($key, $db_conf) && (!isset($db_conf[$key]) || strcmp($conf[$key], $db_conf[$key]) != 0)) {
            #        $fix_columns[] = $key;
            #        $fix_values[]  = $value;
            #    }
            #}
            #if (count($fix_columns) > 0) {
            #    # TODO
            #    $authdb->update('db', array('db_id' => $db->db_id), $fix_columns, $fix_values);
            #}
        }
        else {
            # TODO
            $authdb->execute('insert into {auth_schema_}db (db_name, owner_group_id, db_label, db_env) values (?, ?, ?, ?)', array($name, APP_ALLUSERS_GROUP_ID, $name, $db_env));
            #$db->db_id = $authdb->last_insert_id('db');
            #$db->owner_group_id = APP_ALLUSERS_GROUP_ID;
        }
        if ($trace) trace_exit();
    }

    public function deleteUser ($username, $request = null, &$response = null) {
        global $trace, $context;
        if ($trace) trace_entry();
        $authdb = $context->authdb();
        $delete = (isset($request) && isset($request['delete'])) ? $request['delete'] : 0;

        if ($username === 'ALL' && $delete === 'ALL') {
            $usernames = $authdb->get_column("select username from {auth_schema_}auth_user where username not in ('guest','system')");
            foreach ($usernames as $u) {
                $this->deleteUser($u, array('delete'=>1));
            };
            if (isset($response)) {
                $response['deleted_users'] = $usernames;
            }
            if ($trace) trace_exit();
            return;
        }

        $metadb = $context->metadb();
        $membdb = $context->database("members");
        list($user_id, $alt_user_id) = $authdb->get_row('select user_id, alt_user_id from {auth_schema_}auth_user where username = ?', array($username));
        if (!isset($user_id)) throw new \Exception("The user [$username] is not in the database");
        if ($user_id == APP_GUEST_USER_ID) throw new \Exception("You cannot delete the guest user");
        if ($user_id == APP_SYSTEM_USER_ID) throw new \Exception("You cannot delete the system user");

        $user_tables = array(
            'db_column_set_column' => array('schema'=>'meta', 'table'=>'db_column_set_column', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_column_set'        => array('schema'=>'meta', 'table'=>'db_column_set', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_relationship'      => array('schema'=>'meta', 'table'=>'db_relationship', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_index_column'      => array('schema'=>'meta', 'table'=>'db_index_column', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_index'             => array('schema'=>'meta', 'table'=>'db_index', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_column'            => array('schema'=>'meta', 'table'=>'db_column', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_table'             => array('schema'=>'meta', 'table'=>'db_table', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_schema'            => array('schema'=>'meta', 'table'=>'db_schema', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_env'               => array('schema'=>'meta', 'table'=>'db_env', 'column'=>'owner_group_id', 'var'=>'user_id'),
            'db_view'              => array('schema'=>'meta', 'table'=>'db_view', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'db_chart'             => array('schema'=>'meta', 'table'=>'db_chart', 'column'=>'db_owner_group_id', 'var'=>'user_id'),
            'app_store'            => array('schema'=>'meta', 'table'=>'app_store', 'column'=>'owner_group_id', 'var'=>'user_id'),
            'app_tree'             => array('schema'=>'meta', 'table'=>'app_tree', 'column'=>'owner_group_id', 'var'=>'user_id'),
            'db'                   => array('schema'=>'meta', 'table'=>'db', 'column'=>'owner_group_id', 'var'=>'user_id'),
            'db_auth'              => array('schema'=>'auth', 'table'=>'db', 'column'=>'owner_group_id', 'var'=>'user_id'),

            'am_access_cache'      => array('schema'=>'members', 'table'=>'am_access_cache', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_access_log'        => array('schema'=>'members', 'table'=>'am_access_log', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_access'            => array('schema'=>'members', 'table'=>'am_access', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_aff_lead'          => array('schema'=>'members', 'table'=>'am_aff_lead', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_cc'                => array('schema'=>'members', 'table'=>'am_cc', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_coupon_batch'      => array('schema'=>'members', 'table'=>'am_coupon_batch', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_coupon'            => array('schema'=>'members', 'table'=>'am_coupon', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_error_log'         => array('schema'=>'members', 'table'=>'am_error_log', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_file_download'     => array('schema'=>'members', 'table'=>'am_file_download', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_helpdesk_ticket'   => array('schema'=>'members', 'table'=>'am_helpdesk_ticket', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_invoice_payment'   => array('schema'=>'members', 'table'=>'am_invoice_payment', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_invoice_refund'    => array('schema'=>'members', 'table'=>'am_invoice_refund', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_invoice_log'       => array('schema'=>'members', 'table'=>'am_invoice_log', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_invoice'           => array('schema'=>'members', 'table'=>'am_invoice', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_newsletter_user_subscription' => array('schema'=>'members', 'table'=>'am_newsletter_user_subscription', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_saved_pass'        => array('schema'=>'members', 'table'=>'am_saved_pass', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_session'           => array('schema'=>'members', 'table'=>'am_session', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_store_rebuild'     => array('schema'=>'members', 'table'=>'am_store_rebuild', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_upload'            => array('schema'=>'members', 'table'=>'am_upload', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_user_status'       => array('schema'=>'members', 'table'=>'am_user_status', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_user_user_group'   => array('schema'=>'members', 'table'=>'am_user_user_group', 'column'=>'user_id', 'var'=>'alt_user_id'),
            'am_user'              => array('schema'=>'members', 'table'=>'am_user', 'column'=>'user_id', 'var'=>'alt_user_id'),

            'auth_group_app_access'=> array('schema'=>'auth', 'table'=>'auth_group_app_access', 'column'=>'group_id', 'var'=>'user_id'),
            'auth_perm_role_grant' => array('schema'=>'auth', 'table'=>'auth_perm_role_grant', 'column'=>'group_id', 'var'=>'user_id'),
            'auth_role_user_grant' => array('schema'=>'auth', 'table'=>'auth_role_user_grant', 'column'=>'user_id', 'var'=>'user_id'),
            'auth_role'            => array('schema'=>'auth', 'table'=>'auth_role', 'column'=>'group_id', 'var'=>'user_id'),
            'auth_group_user_memb' => array('schema'=>'auth', 'table'=>'auth_group_user_memb', 'column'=>'user_id', 'var'=>'user_id'),
            'auth_user_email'      => array('schema'=>'auth', 'table'=>'auth_user_email', 'column'=>'user_id', 'var'=>'user_id'),
            'auth_user_password'   => array('schema'=>'auth', 'table'=>'auth_user_password', 'column'=>'user_id', 'var'=>'user_id'),
            'auth_password_reset'  => array('schema'=>'auth', 'table'=>'auth_password_reset', 'column'=>'user_id', 'var'=>'user_id'),
            'auth_group'           => array('schema'=>'auth', 'table'=>'auth_group', 'column'=>'group_id', 'var'=>'user_id'),
            'auth_user'            => array('schema'=>'auth', 'table'=>'auth_user', 'column'=>'user_id', 'var'=>'user_id'),
            'auth_owner'           => array('schema'=>'auth', 'table'=>'auth_owner', 'column'=>'owner_id', 'var'=>'user_id')
        );

        if (isset($response)) {
            $response['username'] = $username;
            $response['user_id'] = $user_id;
            $response['alt_user_id'] = $alt_user_id;

            foreach ($user_tables as $table_key => $tab) {
                $schema = $tab['schema'];
                $table  = $tab['table'];
                $column = $tab['column'];
                $var    = $tab['var'];
                $nrows = $metadb->get("select count(*) from {{$schema}_schema_}$table where $column = ?",
                    array($var === 'alt_user_id' ? $alt_user_id : $user_id));
                if ($nrows) {
                    $response["count_{$schema}_{$table}"] = $nrows;
                }
            }
        }
        
        if ($delete) {
            $current_databases = $authdb->get_hashes("db",
                array("owner_group_id"=>$user_id, "db_owner_group_id"=>$user_id),
                array("db_name","db_connection_id","schema_name"));
            foreach ($current_databases as $currdb) {
                $this->deleteDatabaseMetadata($metadb, $currdb['db_connection_id'], $currdb['schema_name']);
            }

            $this->deleteGroupDirectory($user_id);
            $this->deleteGroupDatabases($user_id);
            foreach ($user_tables as $table_key => $tab) {
                $schema = $tab['schema'];
                $table  = $tab['table'];
                $column = $tab['column'];
                $var    = $tab['var'];
                #debug_print("XXXX");
                $nrows = $metadb->execute("delete from {{$schema}_schema_}$table where $column = ?", array($var === 'alt_user_id' ? $alt_user_id : $user_id));
                if ($nrows) {
                    $response["delete_{$schema}_{$table}"] = $nrows;
                }
            }
        }
        if ($trace) trace_exit();
    }

    public function setupUser ($user_id) {
        global $trace, $context;
        if ($trace) trace_entry();
        $this->createDefaultDirectories($user_id);
        $all_allocated = $this->requestDefaultGroupDatabases($user_id);
        if ($trace) trace_exit($all_allocated);
        return($all_allocated);
    }

    public function createDefaultDirectories ($group_id) {
        global $trace, $context;
        if ($trace) trace_entry();
        $dir = $context->getOwnerDir(null,$group_id);
        foreach (array('upload','export','backup','db','temp','files','web') as $std_subdir) {
            if (! is_dir("$dir/$std_subdir")) $context->mkdir("$dir/$std_subdir");
        }
        if ($trace) trace_exit();
    }

    public function deleteGroupDirectory ($group_id) {
        global $trace, $context;
        if ($trace) trace_entry();
        if ($group_id != APP_ALLUSERS_GROUP_ID && $group_id != APP_SYSTEM_GROUP_ID) {
            $dir = $context->getOwnerDir(null,$group_id);
            if (is_dir($dir)) $context->rmdir($dir);
        }
        if ($trace) trace_exit();
    }

    public function requestDefaultGroupDatabases ($user_id) {
        global $trace, $options, $context;
        if ($trace) trace_entry();

        $allocate_user_databases = (isset($options['allocate_user_databases']) &&
                                    $options['allocate_user_databases'] &&
                                    $options['allocate_user_databases'] !== 'false') ? true : false;

        $all_allocated = true;

        $db_allocated = $this->requestNewDatabase('auth',          $user_id, APP_GUEST_USER_ID, 'Authentication Database', 'mysql',  6, 6, 0, 'Authentication Database - a database where all users and groups are stored');
        $all_allocated = $all_allocated && $db_allocated;

        $db_allocated = $this->requestNewDatabase('meta',          $user_id, APP_GUEST_USER_ID, 'Metadata Database', 'mysql',  6, 6, 0, 'Metadata Database - a database where data about the databases are stored, including configured views, graphs, dashboards, etc.');
        $all_allocated = $all_allocated && $db_allocated;

        if ($allocate_user_databases) {
            $db_allocated = $this->requestNewDatabase('main',          $user_id, $user_id, 'Main User-Defined Database', 'mysql',  7, 7, 0, 'Main User-Defined Database - a database where you can define whatever tables you want.');
            if ($db_allocated) {
                $maindb = $context->database("main");
                $maindb->initializeDatabaseTables();
            }
            $all_allocated = $all_allocated && $db_allocated;

            $db_allocated = $this->requestNewDatabase('app',           $user_id, $user_id, 'Application Database',       'mysql',  6, 6, 0, 'Application Database - a database with preconfigured tables that implement a set of applications you have requested.');
            if ($db_allocated) {
                $appdb = $context->database("app");
                $appdb->initializeDatabaseTables();
            }
            $all_allocated = $all_allocated && $db_allocated;
        }

        $db_allocated = $this->requestNewDatabase('reference',     $user_id, APP_GUEST_USER_ID, 'Reference Database','mysql',  4, 4, 0, 'Reference Database - a database full of useful tables maintained by the system (which you cannot modify).');

        $all_allocated = $all_allocated && $db_allocated;

        if ($trace) trace_exit($all_allocated);
        return($all_allocated);
    }

    public function deleteGroupDatabases ($user_id) {
        global $trace, $options, $context;
        if ($trace) trace_entry();
        $authdb = $context->authdb();
        $dbs = $authdb->get_hashes('select db_name from {auth_schema_}db where db_owner_group_id = ?', array($user_id));
        $all_deallocated = false;
        foreach ($dbs as $db) {
            $deallocated = $this->requestDropDatabase($db['db_name'], $user_id);
            $all_deallocated = $all_deallocated && $deallocated;
        }
        if ($trace) trace_exit($all_deallocated);
        return($all_deallocated);
    }

    /**
     * requestNewDatabase() - create a database record in the auth.db table and allocate the space
     * @param string  db_name            [IN] - the name of the database
     * @param integer owner_group_id     [IN] - the group_id that can view this database as a result of this allocation request
     * @param integer db_owner_group_id  [IN] - the group_id that owns the database. if not equal to owner_group_id, the database must exist and be readable by others
     * @param string  db_label           [IN] - a label to be displayed that describes the database
     * @param string  dbtype             [IN] - [sqlite, mysql, oracle]
     * @param integer user_mode          [IN] - a bitmask. 4=read, 2=write, 1=modify-ddl.
     * @param integer group_mode         [IN] - a bitmask. 4=read, 2=write, 1=modify-ddl.
     * @param integer other_mode         [IN] - a bitmask. 4=read, 2=write, 1=modify-ddl.
     * @param array   db_desc            [IN] - a full sentence or more describing this database
     * @returns boolean allocated        [OUT]- 1=database allocated. 0=not yet allocated
     */
    public function requestNewDatabase ($db_name, $owner_group_id = null, $db_owner_group_id = null, $db_label = null, $dbtype = 'mysql',
                                        $user_mode = 7, $group_mode = 7, $other_mode = 0, $db_desc = null) {
        global $trace, $context, $options;
        if ($trace) trace_entry();
        $allocated = false;
        $site6     = $options['site6'];
        $app_env   = $options['app_env'];
        $db_env    = $context->db_env();
        $authdb    = $context->authdb();
        if (!isset($owner_group_id))    $owner_group_id    = $this->context->getAuthenticatedGroupId();
        if (!isset($db_owner_group_id)) $db_owner_group_id = $owner_group_id;
        if (!isset($db_label))          $db_label          = $db_name;
        if (!isset($db_desc))           $db_desc           = $db_label;

        if ($dbtype === 'sqlite') {
            $schema_name      = $db_name;
            $db_connection_id = 2;
        }
        elseif ($dbtype === 'mysql') {
            $schema_name      = "${site6}_${app_env}_${db_owner_group_id}_${db_env}_${db_name}";
            $db_connection_id = 1;
        }
        else {
            throw new \Exception("Database type [$dbtype] is not yet supported");
        }

        $db_id = $authdb->get('select db_id, dbtype, db_connection_id, status from {auth_schema_}db where db_name = ? and owner_group_id = ? and db_env = ?',
            array($db_name, $owner_group_id, $db_env));

        if (isset($db_id)) throw new \Exception("There is already a database by the name of '$db_name'");

        $sql = <<<EOF
insert into {auth_schema_}db (owner_group_id, db_owner_group_id, db_env, db_name, db_label, dbtype,  db_connection_id, schema_name, status, user_mode, group_mode, other_mode, db_desc) values
               (?, ?, ?, ?, ?, ?, ?, ?, 'R', ?, ?, ?, ?)
EOF;
        $authdb->execute($sql, array($owner_group_id, $db_owner_group_id, $db_env, $db_name, $db_label, $dbtype, $db_connection_id, $schema_name, $user_mode, $group_mode, $other_mode, $db_desc));
        $db_id = $authdb->last_insert_id('db');

        $metadb = $context->metadb();
        $sql = <<<EOF
insert into {meta_schema_}db (db_id, owner_group_id, db_owner_group_id, db_env, db_name, db_label, dbtype,  db_connection_id, schema_name, status, user_mode, group_mode, other_mode, db_desc) values
               (?, ?, ?, ?, ?, ?, ?, ?, ?, 'R', ?, ?, ?, ?)
EOF;
        $metadb->execute($sql, array($db_id, $owner_group_id, $db_owner_group_id, $db_env, $db_name, $db_label, $dbtype, $db_connection_id, $schema_name, $user_mode, $group_mode, $other_mode, $db_desc));

        if ($dbtype === 'sqlite') {
            $newdb = $context->database($db_name);   # this will automatically initialize it with tables
            $authdb->execute("update {auth_schema_}db set status = 'A' where db_id = ?", array($db_id));
            $allocated = true;
        }
        elseif ($dbtype === 'mysql') {
            $authdb->execute("create database if not exists $schema_name");
            $authdb->execute("update {auth_schema_}db set status = 'A' where db_id = ?", array($db_id));
            $allocated = true;
        }

        if ($trace) trace_exit($allocated);
        return($allocated);
    }

    /**
     * requestDropDatabase() - create a database record in the auth.db table and allocate the space
     * @param string  db_name            [IN] - the name of the database
     * @param integer owner_group_id     [IN] - the group_id that can view this database as a result of this allocation request
     * @returns boolean deallocated      [OUT]- true=database deallocated. false=not yet deallocated
     */
    public function requestDropDatabase ($db_name, $owner_group_id = null) {
        global $trace, $context, $options;
        if ($trace) trace_entry();
        $deallocated = false;
        $site6     = $options['site6'];
        $app_env   = $options['app_env'];
        $db_env    = $context->db_env();
        $authdb    = $context->authdb();
        if (!isset($owner_group_id))    $owner_group_id    = $this->context->getAuthenticatedGroupId();

        list($db_id, $dbtype, $db_owner_group_id, $db_connection_id, $schema_name, $status) = $authdb->get_row(
            'select db_id, dbtype, db_owner_group_id, db_connection_id, schema_name, status from {auth_schema_}db where db_name = ? and owner_group_id = ? and db_env = ?',
            array($db_name, $owner_group_id, $db_env));

        if (!isset($db_id)) throw new \Exception("You do not own a database (or a link to a database) by the name of '$db_name'");
        if ($status !== 'A'&& $status !== 'X') throw new \Exception("The '$db_name' database is not in active status and cannot be dropped");

        if ($dbtype === 'sqlite') {
            #throw new \Exception("Dropping a database type [$dbtype] is not yet supported");
            # TODO: not yet done
            #$newdb = $context->database($db_name);   # this will automatically initialize it with tables
            #$authdb->execute("update {auth_schema_}db set status = 'A' where db_id = ?", array($db_id));
            $deallocated = false;
        }
        elseif ($dbtype === 'mysql') {
            $authdb->execute("update {auth_schema_}db set status = 'X' where db_id = ?", array($db_id));  # mark the database as going out of commission
            if ($owner_group_id === $db_owner_group_id) {  # if it's mine to drop, I can drop it
                $metadb    = $context->metadb($owner_group_id);
                $authdb->execute("drop database if exists $schema_name");
                $this->deleteDatabaseMetadata($metadb, $db_connection_id, $schema_name);
                $metadb->execute("delete from {meta_schema_}db where db_id = ?", array($db_id));
            }
            $authdb->execute("delete from {auth_schema_}db where db_id = ?", array($db_id));
            $deallocated = true;
        }
        else {
            throw new \Exception("Dropping a database type [$dbtype] is not yet supported");
        }

        if ($trace) trace_exit($deallocated);
        return($deallocated);
    }

    public function deleteDatabaseMetadata ($metadb, $db_connection_id, $schema_name) {
        global $trace, $context, $options;
        if ($trace) trace_entry();
        $metadb->execute("delete from {meta_schema_}db_column_set_column where column_set_id in (select cs.column_set_id from {meta_schema_}db_schema s inner join {meta_schema_}db_table t on t.schema_id = s.schema_id inner join {meta_schema_}db_column_set cs on cs.table_id = t.table_id where s.db_connection_id = ? and s.schema_name = ?)", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db_index_column      where index_id      in (select i.index_id       from {meta_schema_}db_schema s inner join {meta_schema_}db_table t on t.schema_id = s.schema_id inner join {meta_schema_}db_index i       on i.table_id  = t.table_id where s.db_connection_id = ? and s.schema_name = ?)", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db_column_set        where table_id      in (select t.table_id       from {meta_schema_}db_schema s inner join {meta_schema_}db_table t on t.schema_id = s.schema_id where s.db_connection_id = ? and schema_name = ?)", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db_index             where table_id      in (select t.table_id       from {meta_schema_}db_schema s inner join {meta_schema_}db_table t on t.schema_id = s.schema_id where s.db_connection_id = ? and schema_name = ?)", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db_column            where table_id      in (select t.table_id       from {meta_schema_}db_schema s inner join {meta_schema_}db_table t on t.schema_id = s.schema_id where s.db_connection_id = ? and schema_name = ?)", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db_table             where schema_id     in (select s.schema_id      from {meta_schema_}db_schema s where s.db_connection_id = ? and schema_name = ?)", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db_schema            where db_connection_id = ? and schema_name = ?", array($db_connection_id, $schema_name));
        $metadb->execute("delete from {meta_schema_}db                   where db_connection_id = ? and schema_name = ?", array($db_connection_id, $schema_name));
        if ($trace) trace_exit();
    }

    #/**
    # * createDatabaseTable(request, response) - lists the databases
    # * @param string  db_name    [IN]  - (optional) the database name (default is 'main')
    # * @param string  table_name [IN]  - (optional) the table name
    # * @param boolean success    [OUT] - whether the call succeeded
    # */
    #public function createDatabaseTable ($dbValues) {
    #    global $trace;
    #    if ($trace) trace_entry();
    #    if (is_object($dbValues)) $dbValues = $this->_objectToAssoc($dbValues);
    #    $response = $this->_createDatabaseTable($dbValues);
    #    if ($trace) trace_exit($response);
    #    return($response);
    #}
}

